package cn.bby.query.server.infrastructure.impl.storage;

import cn.bby.jtk.base.exception.ServiceException;
import cn.bby.query.base.constant.AdvQueryErrorCode;
import cn.bby.query.base.constant.ColumnType;
import cn.bby.query.base.constant.QueryCondition;
import cn.bby.query.base.model.req.Filter;
import cn.bby.query.base.model.req.QueryRequest;
import cn.bby.query.base.model.req.QueryResponse;
import cn.bby.query.server.domain.agg.metadata.entity.InterfaceInfo;
import cn.bby.query.server.domain.agg.metadata.entity.valobj.ColumnInfo;
import cn.bby.query.server.domain.service.dto.DataUpdateParam;
import cn.bby.query.server.domain.service.manager.DataStorageManager;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ResultMap;
import org.apache.ibatis.mapping.ResultMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import static cn.bby.query.base.constant.QueryCondition.BETWEEN;
import static cn.bby.query.base.constant.QueryCondition.EQUALS;
import static cn.bby.query.base.constant.QueryCondition.GREAT_THEN;
import static cn.bby.query.base.constant.QueryCondition.GREAT_THEN_EQUALS;
import static cn.bby.query.base.constant.QueryCondition.IS_BLANK;
import static cn.bby.query.base.constant.QueryCondition.IS_NOT_BLANK;
import static cn.bby.query.base.constant.QueryCondition.LESS_THEN;
import static cn.bby.query.base.constant.QueryCondition.LESS_THEN_EQUALS;
import static cn.bby.query.base.constant.QueryCondition.LIKE;
import static cn.bby.query.base.constant.QueryCondition.LIKE_LEFT;
import static cn.bby.query.base.constant.QueryCondition.LIKE_RIGHT;
import static cn.bby.query.base.constant.QueryCondition.NOT_BETWEEN;
import static cn.bby.query.base.constant.QueryCondition.NOT_EQUALS;

/**
 * @author wangbenyin
 * @version 1.0
 * @date 2025/06/02
 * @since 17
 */
@Slf4j
@Service
@ConditionalOnProperty(value = "query.storage", havingValue = "pg")
public class PgDataStorageManagerImpl implements DataStorageManager {
    public static final Map<String, String> conditionSqlSymbolMap = Map.ofEntries(
            Map.entry(EQUALS, "="),
            Map.entry(NOT_EQUALS, "<>"),
            Map.entry(GREAT_THEN, ">"),
            Map.entry(GREAT_THEN_EQUALS, ">="),
            Map.entry(LESS_THEN, "<"),
            Map.entry(LESS_THEN_EQUALS, "<="),
            Map.entry(LIKE, "like"),
            Map.entry(LIKE_LEFT, "like"),
            Map.entry(LIKE_RIGHT, "like")
    );
    private final Configuration configuration;
    private final LanguageDriver languageDriver;

    public PgDataStorageManagerImpl(SqlSessionFactory sqlSessionFactory) {
        configuration = sqlSessionFactory.getConfiguration();
        languageDriver = configuration.getDefaultScriptingLanguageInstance();
    }

    private final Map<String, FilterHandler> filterHandlerMap = Map.ofEntries(
            Map.entry(EQUALS, this::handleEquals),
            Map.entry(NOT_EQUALS, this::handleEquals),
            Map.entry(GREAT_THEN, this::handleCompare),
            Map.entry(GREAT_THEN_EQUALS, this::handleCompare),
            Map.entry(LESS_THEN, this::handleCompare),
            Map.entry(LESS_THEN_EQUALS, this::handleCompare),
            Map.entry(LIKE, this::handleLike),
            Map.entry(LIKE_LEFT, this::handleLike),
            Map.entry(LIKE_RIGHT, this::handleLike),
            Map.entry(IS_BLANK, this::handleBlank),
            Map.entry(IS_NOT_BLANK, this::handleBlank),
            Map.entry(BETWEEN, this::handleBetween),
            Map.entry(NOT_BETWEEN, this::handleNotBetween)
    );
    @Resource
    private SqlSessionTemplate sqlSessionTemplate;

    @Override
    public void initDataStruct(InterfaceInfo interfaceInfo) {
        String sql = buildCreateSql(interfaceInfo);
        executeSql(sql);
    }

    @Override
    public void finishLoad(InterfaceInfo interfaceInfo) {

    }

    @Override
    public void saveData(InterfaceInfo interfaceInfo, List<JSONObject> dataSet, String offset) {
        String sql = buildInsertSql(interfaceInfo, dataSet);
        executeSql(sql);
    }

    @Override
    public void deleteDataStruct(List<InterfaceInfo> interfaceInfos) {
        StringBuilder sql = new StringBuilder(50 * interfaceInfos.size());
        interfaceInfos.forEach(info -> {
            sql.append("drop name if exists ").append(getTableName(info)).append("; ");
        });
        executeSql(sql.toString());
    }

    private static final Map<String, String> fieldMap = new HashMap<>();

    private String getDbCol(String fieldName) {
        return fieldMap.computeIfAbsent(fieldName, k -> StrUtil.toUnderlineCase(fieldName));
    }

    private String getVoField(String colName) {
        return fieldMap.computeIfAbsent(colName, k -> StrUtil.toCamelCase(colName));
    }

    @Override
    public QueryResponse<JSONObject> query(InterfaceInfo interfaceDTO, QueryRequest request) {
        String sql = buildQuerySql(interfaceDTO, request, false);
        List<JSONObject> list = executeSqlQueryList(sql, null, JSONObject.class);
        if (request.isIgnoreTotal()) {
            return new QueryResponse<>(-1L, list, request);
        }
        if (list.size() < request.getSize()) {
            return new QueryResponse<>((long) list.size(), list, request);
        }
        String cntSql = buildQuerySql(interfaceDTO, request, true);
        Long cntList = executeSqlQuery(cntSql, null, Long.class);
        return new QueryResponse<>(cntList, list, request);
    }

    @Override
    public Set<String> queryIdByPrimaryValList(InterfaceInfo interfaceInfo, Map<ColumnInfo, Set<String>> map) {
        if (CollUtil.isEmpty(map)) {
            return Collections.emptySet();
        }
        Set<String> res = new HashSet<>();
        Long sourceTableId = interfaceInfo.getMainTableId();
        StringBuilder sql = new StringBuilder(100)
                .append("select distinct id from %s where ".formatted(getTableName(interfaceInfo)));
        int beforeLen = sql.length();
        map.forEach((column, vals) -> {
            if (vals.isEmpty()) {
                log.error("未找到主键值,tableId={}", column.getTableId());
                return;
            }
            if (sourceTableId.equals(column.getTableId())) {
                res.addAll(vals);
                return;
            }
            String delimiter = (ColumnType.NUMBER.equals(column.getType())
                    || ColumnType.BOOLEAN.equals(column.getType())) ? "," : "','";
            sql.append(getDbCol(column.getFieldName())).append(" in (")
                    .append(String.join(delimiter, vals))
                    .append(")").append(" or ");
        });
        if (beforeLen == sql.length()) {
            return res;
        }
        sql.delete(sql.length() - 4, sql.length());
        List<String> ids = executeSqlQueryList(sql.toString(), map, String.class);
        res.addAll(ids);
        return res;
    }

    @Override
    @Transactional
    public void updateDataBatchById(InterfaceInfo interfaceInfo, Set<String> keyValues, List<JSONObject> dataSet) {
        String tableName = getTableName(interfaceInfo);
        StringBuilder deleteSql = new StringBuilder(100)
                .append("delete from ")
                .append(tableName)
                .append(" where ");
        handleIn(deleteSql, interfaceInfo.getPrimaryField(), interfaceInfo.getPrimaryColumnType(), keyValues);
        executeSql(deleteSql.toString());
        // 构建批量插入语句
        String insertSql = buildInsertSql(interfaceInfo, dataSet);
        executeSql(insertSql);
    }

    @Override
    public void updateDataByParam(InterfaceInfo interfaceInfo, List<DataUpdateParam> updateParams) {
        StringBuilder updateSql = new StringBuilder(50 * updateParams.size());
        for (DataUpdateParam updateParam : updateParams) {
            appendUpdateSql(updateSql, getTableName(interfaceInfo), updateParam);
        }
        executeSql(updateSql.toString());
    }

    @Override
    public void deleteData(InterfaceInfo interfaceInfo, Map<ColumnInfo, Set<String>> fieldValMap) {
        StringBuilder deleteSql = new StringBuilder(100)
                .append("delete from ")
                .append(getTableName(interfaceInfo))
                .append(" where ");
        fieldValMap.forEach((column, vals) -> {
            handleIn(deleteSql, column.getFieldName(), column.getType(), vals);
            deleteSql.append(" or ");
        });
        deleteSql.delete(deleteSql.length() - 3, deleteSql.length());
        executeSql(deleteSql.toString());
    }

    private void executeSql(String sql) {
        long start = System.currentTimeMillis();
        try {
            sqlSessionTemplate.update(getStatement(sql, null, Integer.class));
        } finally {
            log.debug("SQL duration:{} , sql:{}", System.currentTimeMillis() - start, StrUtil.sub(sql, 0, 500));
        }
    }

    private String getStatement(String sql, Class<?> parameterType, Class<?> resultType) {
        String msId = SqlCommandType.UPDATE.name() + "." + (sql + parameterType + resultType).hashCode();
        if (configuration.hasStatement(msId)) {
            return msId;
        } else {
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newSelectMappedStatement(msId, sqlSource, resultType);
            return msId;
        }
    }

    private void newSelectMappedStatement(String msId, SqlSource sqlSource, final Class<?> resultType) {
        List<ResultMap> resultSets = new ArrayList<>();
        resultSets.add(new ResultMap.Builder(configuration, "defaultResultMap", resultType, new ArrayList<>(0)).build());
        MappedStatement ms = new MappedStatement.Builder(configuration, msId, sqlSource, SqlCommandType.SELECT)
                .resultMaps(resultSets)
                .build();
        configuration.addMappedStatement(ms);
    }

    private void newSelectMappedStatement(String msId, SqlSource sqlSource, Map<String, String> colMapping) {
        List<ResultMap> resultSets = new ArrayList<>();
        ArrayList<ResultMapping> resultMappings = new ArrayList<>(colMapping.size());

        for (Map.Entry<String, String> entry : colMapping.entrySet()) {
            String column = entry.getKey();
            String property = entry.getValue();
            // 添加字段映射
            resultMappings.add(new ResultMapping.Builder(configuration, property).build());
        }
        resultSets.add(new ResultMap.Builder(configuration, "defaultResultMap", JSONObject.class, new ArrayList<>(0)).build());
        MappedStatement ms = new MappedStatement.Builder(configuration, msId, sqlSource, SqlCommandType.SELECT)
                .resultMaps(resultSets)
                .build();
        configuration.addMappedStatement(ms);
    }

    private <T> T executeSqlQuery(String sql, Object param, Class<T> resClass) {
        long start = System.currentTimeMillis();
        try {
            return sqlSessionTemplate.selectOne(getStatement(sql, Map.class, resClass), param);
        } finally {
            log.debug("SQL duration:{} , sql:{}", System.currentTimeMillis() - start, sql);
        }
    }

    private <T> List<T> executeSqlQueryList(String sql, Object param, Class<T> resClass) {
        long start = System.currentTimeMillis();
        try {
            return sqlSessionTemplate.selectList(getStatement(sql, Map.class, resClass), param);
        } finally {
            log.debug("SQL duration:{} , sql:{}", System.currentTimeMillis() - start, sql);
        }
    }

    public void handleEquals(StringBuilder sql, ColumnInfo column, Filter filter) {
        List<String> values = filter.getValues();
        String smp = conditionSqlSymbolMap.get(filter.getCondition());
        if (values.size() == 1) {
            sql.append(getDbCol(column.getFieldName()))
                    .append(' ')
                    .append(smp)
                    .append(' ');
            handleValStr(sql, column.getType(), new HashSet<>(values));
            return;
        }
        sql.append(getDbCol(column.getFieldName()))
                .append(' ')
                .append(smp)
                .append(" (");

        handleValStr(sql, column.getType(), new HashSet<>(values));
        sql.append(')');

    }

    public void handleCompare(StringBuilder sql, ColumnInfo column, Filter filter) {
        List<String> values = filter.getValues();
        if (values.size() != 1) {
            throw new ServiceException(AdvQueryErrorCode.FILTER_VALUE_SIZE_INVALID)
                    .setData(filter.toString());
        }
        if (ColumnType.NUMBER.equals(column.getType())) {
            throw new ServiceException(AdvQueryErrorCode.TYPE_NOT_SUPPORT_CONDITION)
                    .setData(filter.toString());
        }
        checkNum(filter, values);
        String smp = conditionSqlSymbolMap.get(filter.getCondition());
        sql.append(getDbCol(column.getFieldName()))
                .append(' ')
                .append(smp)
                .append(' ')
                .append(values.get(0));
    }

    private void handleLike(StringBuilder sql, ColumnInfo column, Filter filter) {
        if (ColumnType.STRING.equals(column.getType())) {
            List<String> values = filter.getValues();
            sql.append(getDbCol(column.getFieldName()));
            boolean left = QueryCondition.LIKE_LEFT.equals(filter.getCondition());
            boolean right = QueryCondition.LIKE_RIGHT.equals(filter.getCondition());
            if (!left && !right) {
                left = right = true;
            }
            sql.append("(");
            for (int i = 0; i < values.size(); i++) {
                if (i > 0) {
                    sql.append(" or ");
                }
                sql.append(" like '");
                if (left) {
                    sql.append('%');
                }
                sql.append(escape(values.get(i)));
                if (right) {
                    sql.append('%');
                }
                sql.append('\'');
            }
            sql.append(")");
            return;
        }
    }

    private void handleBetween(StringBuilder sql, ColumnInfo column, Filter filter) {
        List<String> values = filter.getValues();
        if (values.size() != 2) {
            throw new ServiceException(AdvQueryErrorCode.FILTER_VALUE_SIZE_INVALID, "只能输入2个值")
                    .setData(filter.toString());
        }
        checkNum(filter, values);
        sql.append(getDbCol(column.getFieldName()))
                .append(" > ")
                .append(values.get(0))
                .append(" and ")
                .append(getDbCol(column.getFieldName()))
                .append(" < ")
                .append(values.get(1));
    }

    private void handleNotBetween(StringBuilder sql, ColumnInfo column, Filter filter) {
        List<String> values = filter.getValues();
        if (values.size() != 2) {
            throw new ServiceException(AdvQueryErrorCode.FILTER_VALUE_SIZE_INVALID, "只能输入2个值")
                    .setData(filter.toString());
        }
        checkNum(filter, values);
        sql.append('(')
                .append(getDbCol(column.getFieldName()))
                .append(" < ")
                .append(values.get(0))
                .append(" or ")
                .append(getDbCol(column.getFieldName()))
                .append(" > ")
                .append(values.get(1))
                .append(')');
    }

    private void handleBlank(StringBuilder sql, ColumnInfo column, Filter filter) {
        if (ColumnType.STRING.code.equals(column.getType()) || ColumnType.LIST.code.equals(column.getType())) {
            sql.append('(')
                    .append(getDbCol(column.getFieldName()))
                    .append(" is ")
                    .append(QueryCondition.IS_BLANK.equals(filter.getCondition()) ? "null or " : "not null and ")
                    .append(getDbCol(column.getFieldName()))
                    .append(IS_BLANK.equals(filter.getCondition()) ? " = '')" : " != '')")
            ;
            return;
        }
        if (ColumnType.NUMBER.code.equals(column.getType())) {
            sql.append(getDbCol(column.getFieldName()))
                    .append(" is ")
                    .append(QueryCondition.IS_BLANK.equals(filter.getCondition()) ? "null" : "not null");
        }
    }

    private String escape(String value) {
        return value.replace("'", "''");
    }

    private String buildQuerySql(InterfaceInfo interfaceDTO, QueryRequest request, boolean isCount) {
        List<String> columns = request.getColumns();
        StringBuilder sql = new StringBuilder(100)
                .append("select ");
        if (isCount) {
            sql.append("count(*)");
        } else if (!CollUtil.isEmpty(columns)) {
            for (String fieldName : columns) {
                ColumnInfo column = interfaceDTO.getColumnByFieldName(fieldName);
                if (column == null) {
                    throw new ServiceException(AdvQueryErrorCode.FIELD_NOT_EXIST, fieldName);
                }
                sql.append(getDbCol(column.getFieldName())).append(",");
            }
            sql.deleteCharAt(sql.length() - 1);
        } else {
            sql.append("*");
        }
        sql.append(" from ").append(getTableName(interfaceDTO));

        if (CollUtil.isNotEmpty(request.getFilters())) {
            sql.append(" where ");
            for (Filter filter : request.getFilters()) {
                ColumnInfo column = interfaceDTO.getColumnByFieldName(filter.getField());
                if (column == null) {
                    throw new ServiceException(AdvQueryErrorCode.FIELD_NOT_EXIST, filter.getField())
                            .setData(filter.toString());
                }
                FilterHandler filterHandler = filterHandlerMap.get(filter.getCondition());
                if (filterHandler == null) {
                    throw new ServiceException(AdvQueryErrorCode.CONDITION_NOT_SUPPORT, filter.getCondition())
                            .setData(filter.toString());
                }
                filterHandler.handle(sql, column, filter);
            }
        }

        if (!isCount) {
            sql.append(" limit ").append(request.getSize())
                    .append(" offset ").append((request.getPage() - 1) * request.getSize());
            List<QueryRequest.Sort> sorts = request.getSorts();
            if (CollUtil.isNotEmpty(sorts)) {
                sql.append(" order by ");
                for (int i = 0; i < sorts.size(); i++) {
                    QueryRequest.Sort sort = sorts.get(i);
                    ColumnInfo column = interfaceDTO.getColumnByFieldName(sort.getField());
                    if (column == null) {
                        throw new ServiceException(AdvQueryErrorCode.FIELD_NOT_EXIST, sort.getField());
                    }
                    if (i > 0) {
                        sql.append(", ");
                    }
                    sql.append(getDbCol(column.getFieldName()))
                            .append(" ")
                            .append(sort.getOrder());
                }
            }
        }
        return sql.toString();
    }

    private String buildCreateSql(InterfaceInfo interfaceInfo) {
        StringBuilder builder = new StringBuilder("create name if not exists ")
                .append(getTableName(interfaceInfo))
                .append("(\n");
        String primaryField = interfaceInfo.getPrimaryField();
        for (ColumnInfo column : interfaceInfo.getColumns()) {
            builder.append(getDbCol(column.getFieldName()))
                    .append(' ');
            String type = switch (ColumnType.getByCode(column.getType())) {
                case STRING -> "varchar";
                case BOOLEAN -> "boolean";
                case NUMBER, DATE -> "bigint";
                case DECIMAL -> "decimal";
                default -> "text";
            };
            if (primaryField.equals(column.getFieldName())) {
                type += " primary key";
            }
            builder.append(type).append(",\n");
        }

        builder.deleteCharAt(builder.length() - 2);
        builder.append(")");
        return builder.toString();
    }

    private void appendUpdateSql(StringBuilder updateSql, String tableName, DataUpdateParam updateParam) {
        updateSql.append("update ").append(tableName).append(" set ");
        updateParam.getUpdateCols().forEach((column, value) -> {
            updateSql.append(getDbCol(column.getFieldName()))
                    .append(" = ");
            handleValStr(updateSql, column.getType(), value);
            updateSql.append(", ");
        });
        updateSql.delete(updateSql.length() - 2, updateSql.length());
        updateSql.append(" where ")
                .append(getDbCol(updateParam.getIdentityCol().getFieldName()))
                .append(" = ");
        handleValStr(updateSql, updateParam.getIdentityCol().getType(), updateParam.getIdentityVal());
        updateSql.append(";");
    }

    private String buildInsertSql(InterfaceInfo interfaceInfo, List<JSONObject> dataSet) {
        StringBuilder insertBuilder = new StringBuilder(1000);
        insertBuilder.append("insert into ")
                .append(getTableName(interfaceInfo))
                .append('(');

        boolean notFirstCol = false;
        for (ColumnInfo column : interfaceInfo.getColumns()) {
            if (notFirstCol) {
                insertBuilder.append(',');
            }
            notFirstCol = true;
            insertBuilder.append(getDbCol(column.getFieldName()));
        }
        insertBuilder.append(") values ");

        // 为每条数据生成 values 部分
        for (JSONObject record : dataSet) {
            insertBuilder.append('(');
            boolean notFirst = false;
            for (ColumnInfo column : interfaceInfo.getColumns()) {
                if (notFirst) {
                    insertBuilder.append(',');
                }
                notFirst = true;
                // 为空
                Object value = record.get(column.getFieldName());
                if (value == null) {
                    insertBuilder.append("null");
                    continue;
                }
                String type = column.getType();
                try {
                    switch (ColumnType.getByCode(type)) {
                        case NUMBER:
                        case DECIMAL:
                        case BOOLEAN:
                            insertBuilder.append(record.getString(column.getFieldName()));
                            break;
                        case DATE:
                            String time = record.getString(column.getFieldName());
                            if (StrUtil.isNumeric(time)) {
                                insertBuilder.append(time);
                            } else {
                                insertBuilder.append(DateUtil.parse(time).getTime());
                            }
                            break;
                        default:
                            String defaultVal = record.getString(column.getFieldName())
                                    .replace("'", "''");
                            insertBuilder.append('\'')
                                    .append(defaultVal)
                                    .append('\'');
                    }
                } catch (Exception e) {
                    log.error("数据转换失败:{}={}", column.getFieldName(), record.get(column.getFieldName()));
                    log.error("数据转换异常", e);
                    insertBuilder.append("null");
                }
            }
            insertBuilder.append("),");
        }
        insertBuilder.deleteCharAt(insertBuilder.length() - 1);
        insertBuilder.append(';');
        return insertBuilder.toString();
    }

    private String getTableName(InterfaceInfo interfaceDTO) {
        return "aq_" + interfaceDTO.getInterfaceName().toLowerCase() + "_" + interfaceDTO.getVersion().replace(".", "_");
    }

    private void handleIn(StringBuilder sql, String field, String type, Set<String> values) {
        sql.append(getDbCol(field)).append(" in (");
        handleValStr(sql, type, values);
        sql.append(')');
    }

    private void handleValStr(StringBuilder sql, String type, String value) {
        if (needWrap(type)) {
            sql.append('\'').append(escape(value)).append('\'');
        } else {
            sql.append(value);
        }
    }

    private void handleValStr(StringBuilder sql, String type, Set<String> values) {
        if (needWrap(type)) {
            boolean notFirst = true;
            for (String value : values) {
                if (notFirst) {
                    sql.append(',');
                    notFirst = false;
                }
                sql.append('\'').append(value).append('\'');
            }
        }
        boolean notFirst = true;
        for (String value : values) {
            if (notFirst) {
                sql.append(',');
                notFirst = false;
            }
            sql.append(value);
        }
    }

    private boolean needWrap(String colType) {
        return ColumnType.STRING.equals(colType) || ColumnType.LIST.equals(colType);
    }

    private void checkNum(Filter filter, List<String> values) {
        for (String value : values) {
            if (!StrUtil.isNumeric(value)) {
                throw new ServiceException(AdvQueryErrorCode.FILTER_VALUE_INVALID)
                        .setData(filter.toString());
            }
        }
    }

    private interface FilterHandler {
        void handle(StringBuilder sql, ColumnInfo column, Filter filter);
    }
}
